The present invention relates generally to the field of database management, and more particularly to synopsis tables for databases.
In database systems, materialized query tables (MQTs), automatic summary tables (ASTs), and indexes on expressions have become commonplace and are widely used in the database management industry.
A materialized query table is a table whose definition is based upon the result of a query. The data that is contained in an MQT is derived from one or more tables on which the materialized query table definition is based. An automatic summary table is considered to be a specialized type of MQT that contains a GROUP BY clause summarizing data from the tables that are referenced. MQTs can significantly improve the performance of queries, especially complex queries. MQTs may be large or a large number of MQTs may be required so that they can be exploited for a large spectrum of queries.
Generated columns are another way of materializing expression results in a table. These expression results are computed and stored as extra columns in a table and along with an index can be exploited for performance by queries with predicates using these expressions. The materialization of these expressions has storage overhead both in the table and the accompanying index if present.
Indexes on expressions or function based indexes are yet another mechanism of pre-computing expressions on columns of a table and storing these along with pointers to or the address of the rows in the table. These indexes can be used to optimize table access when the expressions are used in predicates in queries.
Metadata in the form of synopsis tables are often used to skip strides (or zones or regions) of a table. These synopsis tables may store the minimum, maximum and/or a bit filter of all the values in a table column belonging to a stride or zone or region. For example a set of 1000 rows (or a page of rows) may make up a stride. Knowing the minimum and maximum value of a column for these rows may allow the database to skip the stride (or page) if it could be determined based on a predicate in the query that the stride (or page) does not contain any row of interest to query. Synopsis tables are significantly smaller than the base tables that they are associated with. For a stride size of 1000 rows the synopsis tables could have 1/1000 of the number of rows in the base table